package com.stm.bi.report;

import com.stm.base.api.RegionRemoteService;
import com.stm.base.dto.query.RegionListQry;
import com.stm.base.dto.vo.RegionDetailVO;
import com.stm.base.dto.vo.RegionVO;
import com.stm.bi.core.util.DateUtils;
import com.stm.bi.dto.query.RegionalSaleQry;
import com.stm.bi.dto.vo.RegionalCityVO;
import com.stm.bi.dto.vo.RegionalCustomerVO;
import com.stm.bi.model.Chart;
import com.stm.bi.model.DateRange;
import com.stm.framework.core.context.UserHolder;
import com.stm.framework.core.util.FileResourceLoader;
import com.stm.framework.dbcp.JdbcService;
import com.stm.framework.dto.Response;
import com.stm.saas.api.CalendarCycleRemoteService;
import com.stm.saas.api.ProductConfigRemoteService;
import com.stm.saas.dto.vo.ProductConfigVO;
import com.stm.saas.model.CalendarCycle;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

import static com.stm.bi.constant.IndexConstants.NUMBER_OF_TRANSACTIONS_FIELD;
import static com.stm.bi.constant.IndexConstants.PROVINCE_CODE_FIELD;
import static com.stm.bi.constant.PlanConstants.TYPE_MONTH;


/**
 * @author liuyx
 * @date 2025/2/18 10:22
 */
@Component
public class RegionalProportionOfCustomersChartQueryExe extends AbsChartQueryExe<RegionalSaleQry, Chart>{
    @Resource
    private JdbcService jdbcService;

    @Resource
    private CalendarCycleRemoteService calendarCycleRemoteService;

    @Resource
    private RegionRemoteService regionRemoteService;

    @Resource
    private ProductConfigRemoteService productConfigRemoteService;

    @Override
    public Chart<RegionalCustomerVO> query(Long orgId, String orgNo, RegionalSaleQry regionalSaleQry) {
        RegionalCustomerVO vo = new RegionalCustomerVO();
        List<RegionalCityVO> cityTableData = new ArrayList<>();
        Chart chart = new Chart();
        List<List<Object>> datas = new ArrayList<>();
        List<String> items = chart.getItems();
        // 查客数
        CalendarCycle cyc = calendarCycleRemoteService.getByOrg().get();
        DateRange dateRange = DateUtils.getYearFirstAndEnd(LocalDate.now().toString(), cyc.getMonth());

        Response<ProductConfigVO> prodResp = productConfigRemoteService.selectByOrgId(UserHolder.getOrgId());
        String salesDefinition = "dealing";
        if(prodResp != null && prodResp.getData() != null) {
            salesDefinition = prodResp.getData().getSalesDefinition();
        }
        if("delivery".equals(salesDefinition)) {
            salesDefinition = "dealing";
        }

        String selectSql = FileResourceLoader.getResourceAsString("/sql/adsBaseProvinceSales.sql");
        List<Map> maps = jdbcService.selectList(selectSql, new Object[]{
                orgId,
                regionalSaleQry.getDeptId(),
                TYPE_MONTH,
                salesDefinition,
                dateRange.getStartDate(),
                dateRange.getEndDate(),
                NUMBER_OF_TRANSACTIONS_FIELD});


        if (maps.isEmpty()) {
            if (regionalSaleQry.getRegionCode() == null || regionalSaleQry.getRegionCode().isEmpty()) {
                vo.setRegionName("全国");
            } else {
                Response<RegionDetailVO> regionDetailVOResponse = regionRemoteService.selectById(Long.valueOf(regionalSaleQry.getRegionCode()));
                if (regionDetailVOResponse.isSuccess() && regionDetailVOResponse.getData() != null) {
                    vo.setRegionName(regionDetailVOResponse.getData().getName()).setCityCode(regionalSaleQry.getRegionCode());
                }
            }
            return chart.setExtend(vo);
        }
        maps.sort(Comparator.comparing((Map m) -> ((BigDecimal) m.get(NUMBER_OF_TRANSACTIONS_FIELD))).reversed());
        //总客数
        double totalNum = maps.stream().map(m -> (BigDecimal) m.get(NUMBER_OF_TRANSACTIONS_FIELD)).filter(Objects::nonNull).mapToDouble(BigDecimal::doubleValue).sum();

        // 查全国
        items.add("老客占比");
        items.add("新客占比");
        double newCustomQty = maps.stream().map(m -> (BigDecimal) m.get("new_customers")).filter(Objects::nonNull).mapToDouble(BigDecimal::doubleValue).sum();
        double oldCustomQty = maps.stream().map(m -> (BigDecimal) m.get("old_customers")).filter(Objects::nonNull).mapToDouble(BigDecimal::doubleValue).sum();
        double totalCustomQty = newCustomQty + oldCustomQty;
        List<Map> cityMaps = new ArrayList<>();
        if (regionalSaleQry.getRegionCode() == null || regionalSaleQry.getRegionCode().isEmpty()) {
            List<Object> data = new ArrayList<>();
            List<Object> data2 = new ArrayList<>();
            vo.setRegionName("全国").setSalesVolume(BigDecimal.valueOf(100));
            // 饼图
            data2.add("老客占比");
            data.add("新客占比");
            if (totalCustomQty != 0.0) {
                data2.add(BigDecimal.valueOf(100 * oldCustomQty / totalCustomQty).setScale(2, RoundingMode.HALF_UP));
                data.add(BigDecimal.valueOf(100 * newCustomQty / totalCustomQty).setScale(2, RoundingMode.HALF_UP));
            }else{
                data.add(BigDecimal.ZERO);
                data2.add(BigDecimal.ZERO);
            }
            datas.add(data2);
            datas.add(data);

            // 城市顾客占比排名前五
            selectSql = FileResourceLoader.getResourceAsString("/sql/adsBaseCitySales.sql");
            cityMaps = jdbcService.selectList(selectSql, new Object[]{
                    orgId,
                    regionalSaleQry.getDeptId(),
                    TYPE_MONTH,
                    salesDefinition,
                    dateRange.getStartDate(),
                    dateRange.getEndDate(),
                    NUMBER_OF_TRANSACTIONS_FIELD});

        } else {
            // 总数筛选省份
            double provinceTotalNum = maps.stream().filter(e -> e.get(PROVINCE_CODE_FIELD).equals(regionalSaleQry.getRegionCode())).map(m -> (BigDecimal) m.get(NUMBER_OF_TRANSACTIONS_FIELD)).filter(Objects::nonNull).mapToDouble(BigDecimal::doubleValue).sum();
            if (provinceTotalNum != 0) {
                // 具体的省份
                double finalTotalNum = provinceTotalNum;
                maps.stream().filter(e -> e.get(PROVINCE_CODE_FIELD).equals(regionalSaleQry.getRegionCode())).findFirst().ifPresent(m -> {
                    vo.setRegionName((String) m.get("province_name"))
                            .setSalesVolume(BigDecimal.valueOf(100 * provinceTotalNum / totalNum).setScale(2, RoundingMode.HALF_UP));
                });
                // 饼图
                List<Object> data2 = new ArrayList<>();
                double tran2 = maps.stream().filter(e -> e.get(PROVINCE_CODE_FIELD).equals(regionalSaleQry.getRegionCode())).map(m -> (BigDecimal) m.get("old_customers")).filter(Objects::nonNull).mapToDouble(BigDecimal::doubleValue).sum();
                data2.add("老客占比");
                data2.add(BigDecimal.valueOf(100 * tran2 / finalTotalNum).setScale(2, RoundingMode.HALF_UP));
                datas.add(data2);
                List<Object> data = new ArrayList<>();
                data.add("新客占比");
                double tran = maps.stream().filter(e -> e.get(PROVINCE_CODE_FIELD).equals(regionalSaleQry.getRegionCode())).map(m -> (BigDecimal) m.get("new_customers")).filter(Objects::nonNull).mapToDouble(BigDecimal::doubleValue).sum();
                data.add(BigDecimal.valueOf(100 * tran / finalTotalNum).setScale(2, RoundingMode.HALF_UP));
                datas.add(data);
            } else {
                Response<RegionDetailVO> regionDetailVOResponse = regionRemoteService.selectById(Long.valueOf(regionalSaleQry.getRegionCode()));
                if (regionDetailVOResponse.isSuccess() && regionDetailVOResponse.getData() != null) {
                    vo.setRegionName(regionDetailVOResponse.getData().getName()).setCityCode(regionalSaleQry.getRegionCode());
                }
            }
            // 城市顾客占比排名前五
            RegionListQry regionQry = new RegionListQry();
            regionQry.setParentId(Long.valueOf(regionalSaleQry.getRegionCode()));
            Response<List<RegionVO>> regionResponse = regionRemoteService.selectList(regionQry);
            if (regionResponse.isSuccess() || !regionResponse.getData().isEmpty()) {
                List<RegionVO> regions = regionResponse.getData();
                List<String> cityIds = regions.stream().map(RegionVO::getId).filter(Objects::nonNull).map(Object::toString).collect(Collectors.toList());
                String cityIdsStr = String.join(",", cityIds);
                selectSql = FileResourceLoader.getResourceAsString("/sql/adsBaseCitySales.sql");
                selectSql = selectSql.replace("ORDER BY ? desc", "AND city_code IN (" + cityIdsStr + ") ORDER BY ? desc");

                cityMaps = jdbcService.selectList(selectSql, new Object[]{
                        orgId,
                        regionalSaleQry.getDeptId(),
                        TYPE_MONTH,
                        salesDefinition,
                        dateRange.getStartDate(),
                        dateRange.getEndDate(),
                        NUMBER_OF_TRANSACTIONS_FIELD});

            }
        }
        Map<String, Map> result = cityMaps.stream()
                .collect(Collectors.groupingBy(
                        m -> (String) m.get("city_code"),
                        Collectors.collectingAndThen(
                                Collectors.toList(),
                                list -> {
                                    // 销量
                                    BigDecimal totalQty = list.stream()
                                            .map(m -> (BigDecimal) m.get(NUMBER_OF_TRANSACTIONS_FIELD))
                                            .filter(Objects::nonNull)
                                            .reduce(BigDecimal.ZERO, BigDecimal::add);
                                    // 新客数
                                    BigDecimal yearNewCustomers = list.stream()
                                            .map(m -> (BigDecimal) m.get("new_customers"))
                                            .filter(Objects::nonNull)
                                            .reduce(BigDecimal.ZERO, BigDecimal::add);
                                    // 老客数
                                    BigDecimal yearOldCustomers = list.stream()
                                            .map(m -> (BigDecimal) m.get("old_customers"))
                                            .filter(Objects::nonNull)
                                            .reduce(BigDecimal.ZERO, BigDecimal::add);

                                    // 创建一个新的Map来存储汇总数据
                                    Map<String, Object> aggregatedMap = new HashMap<>();
                                    for (Map map : list) {
                                        aggregatedMap.putAll(map);
                                    }
                                    aggregatedMap.put("yearTotalQty", totalQty);
                                    aggregatedMap.put("yearNewCustomers", yearNewCustomers);
                                    aggregatedMap.put("yearOldCustomers", yearOldCustomers);
                                    return aggregatedMap;
                                }
                        )
                ));
        // 将分组后的结果转换为列表并按 numberOfTransactions 字段倒序排序
        List<Map.Entry<String, Map>> sortedList = new ArrayList<>(result.entrySet());
        sortedList.sort((e1, e2) -> {
            int count1 = ((Number) e1.getValue().get("yearTotalQty")).intValue();
            int count2 = ((Number) e2.getValue().get("yearTotalQty")).intValue();
            return Integer.compare(count2, count1); // 倒序排序
        });
        sortedList.removeIf(e -> e.getKey().equals("-99") || e.getKey().isEmpty());
        // 截取前五个条目
        List<Map.Entry<String, Map>> topFiveEntries = sortedList.stream()
                .limit(5)
                .collect(Collectors.toList());

        // 将截取后的列表转换回 Map
        Map<String, Map> topFiveResult = new LinkedHashMap<>();
        for (Map.Entry<String, Map> entry : topFiveEntries) {
            topFiveResult.put(entry.getKey(), entry.getValue());
        }
        for (String key : topFiveResult.keySet()) {
            RegionalCityVO regionalCity = new RegionalCityVO();
            Map cityMap = topFiveResult.get(key);
            BigDecimal totalQty = (BigDecimal) cityMap.get("yearTotalQty");
            if (totalNum != 0) {
                regionalCity.setTotalCustomProportion(BigDecimal.valueOf(100 * totalQty.doubleValue() / totalNum).setScale(2, RoundingMode.HALF_UP));
            } else {
                continue;
            }
            if (totalQty != null && totalQty.doubleValue() != 0) {
                BigDecimal tran = (BigDecimal) cityMap.get("yearNewCustomers");
                regionalCity.setNewCustomProportion(BigDecimal.valueOf(100 * tran.doubleValue() / totalQty.doubleValue()).setScale(2, RoundingMode.HALF_UP));
                tran = (BigDecimal) cityMap.get("yearOldCustomers");
                regionalCity.setOldCustomProportion(BigDecimal.valueOf(100 * tran.doubleValue() / totalQty.doubleValue()).setScale(2, RoundingMode.HALF_UP));
            } else {
                continue;
            }
            if (key.equals("110100000000")) {
                regionalCity.setCityName("北京市");
            } else if (key.equals("120100000000")) {
                regionalCity.setCityName("天津市");
            } else if (key.equals("310100000000")) {
                regionalCity.setCityName("上海市");
            } else if (key.equals("500100000000")) {
                regionalCity.setCityName("重庆市");
            } else {
                regionalCity.setCityName((String) topFiveResult.get(key).get("city_name"));
            }
            regionalCity.setCityCode(key);
            cityTableData.add(regionalCity);

        }
        chart.setItems(items).setDatas(datas);
        vo.setCityTableData(cityTableData);
        return chart.setExtend(vo);
    }

    @Override
    public String getReportName() {
        return "区域顾客占比";
    }
}
